<?php
class Model_website extends Model
{
    function __construct()
    {
        parent::__construct();
    }

    /*
     -------------------------------------
     * model: Account
     * 1. Survey, 2. campaign, 3. information, 4. connect, 5.Buzzscore, 6.history
     -------------------------------------
     */
    public function getSurveyById($sid){
        try{
            $sth = $this->db->prepare("select * from survey where sid = :sid");
            $sth->execute(array(
                ':sid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyChildById($scid){
        try{
            $sth = $this->db->prepare("select * from survey_child where scid = :scid order by survey_child_position asc");
            $sth->execute(array(
                ':scid' => $scid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyChildBySid($sid){
        try{
            $sth = $this->db->prepare("select * from survey_child where sid = :sid order by survey_child_position asc");
            $sth->execute(array(
                ':sid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionParentBySid($sid){
        try{
            $sth = $this->db->prepare("select * from question where parent_qid = 0 and scid = :scid order by question_position desc");
            $sth->execute(array(
                ':scid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionByParentId($parent){
        try{
            $sth = $this->db->prepare("select * from question where parent_qid=:parent_qid order by question_position asc");
            $sth->execute(array(
                ':parent_qid' => $parent
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionById($qid){
        try{
            $sth = $this->db->prepare("select * from question where qid = :qid");
            $sth->execute(array(
                ':qid' => $qid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionChildById($qcid){
        try{
            $sth = $this->db->prepare("select * from question_child where qcid = :qcid");
            $sth->execute(array(
                ':qcid' => $qcid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionChildByQid($qid){
        try{
            $sth = $this->db->prepare("select * from question_child where qid = :qid");
            $sth->execute(array(
                ':qid' => $qid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getQuestionParentBySidAsc($sid){
        try{
            $sth = $this->db->prepare("select * from question where parent_qid = 0 and scid = :scid order by question_position asc");
            $sth->execute(array(
                ':scid' => $sid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getUserSurveyByUid($uid){
        try{
            $sth = $this->db->prepare("select * from user_survey where uid =:uid");
            $sth->execute(array(
                ':uid' => $uid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getUserSurveyByScidAndUid($uid, $scid){
        try{
            $sth = $this->db->prepare("select * from user_survey where uid =:uid and scid=:scid");
            $sth->execute(array(
                ':uid' => $uid,
                ':scid' => $scid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSurveyCountByScid($scid){
        try{
            $sth = $this->db->prepare("SELECT count(usid) as number FROM user_survey WHERE DATE(surveyed_time) = DATE(NOW()) and scid=:scid");
            $sth->execute(array(
                ':scid' => $scid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getAnsweredByUsid($usid){
        try{
            $sth = $this->db->prepare("select * from answered where usid=:usid");
            $sth->execute(array(
                ':usid' => $usid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getAnsweredChildren($parent){
        try{
            $sth = $this->db->prepare("select * from answered where question_parent=:question_parent");
            $sth->execute(array(
                ':question_parent' => $parent
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getAnsweredByUsidAndQid($usid, $qid){
        try{
            $sth = $this->db->prepare("select * from answered where usid=:usid and qid=:qid");
            $sth->execute(array(
                ':usid' => $usid,
                ':qid' => $qid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addUserSurvey($uid, $scid, $number_question, $number_answered, $surveyed_time){
        try{
            $sth = $this->db->prepare("insert into user_survey(`uid`, `scid`,`number_question`, `number_answered`, `surveyed_time`)
            values( :uid, :scid, :number_question, :number_answered, :surveyed_time)");
            $sth->execute(array(
                ':uid' => $uid,
                ':scid' => $scid,
                ':number_question' => $number_question,
                ':number_answered' => $number_answered,
                ':surveyed_time' => $surveyed_time
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addAnswered($usid, $qid, $question_parent, $answered_id, $answered_value, $answered_time){
        try{
            $sth = $this->db->prepare("insert into answered(`usid`, `qid`,`question_parent`, `answered_id`, `answered_value`, `answered_time`)
            values( :usid, :qid, :question_parent, :answered_id, :answered_value, :answered_time)");
            $sth->execute(array(
                ':usid' => $usid,
                ':qid' => $qid,
                ':question_parent' => $question_parent,
                ':answered_id' => $answered_id,
                ':answered_value' => $answered_value,
                ':answered_time' => $answered_time
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateAnswered($ansid, $answered_id, $answered_value, $answered_time){
        try{
            $sth = $this->db->prepare("update answered set answered_id =:answered_id, answered_value =:answered_value, answered_time =:answered_time where ansid =:ansid");
            $sth->execute(array(
                ':answered_id' => $answered_id,
                ':answered_value' => $answered_value,
                ':answered_time' => $answered_time,
                ':ansid' => $ansid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function removeAnsweredByQuestionParent($parent){
        try{
            $sth = $this->db->prepare("delete from answered where question_parent =:question_parent");
            $sth->execute(array(
                ':question_parent' => $parent
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }


    public function getUserById($uid){
        try{
            $sth = $this->db->prepare("select * from users where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getProvinceById($pid){
        try{
            $sth = $this->db->prepare("select * from province where pid=:pid");
            $sth->execute(array(
                ':pid' => $pid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getChildrenByUid($uid){
        try{
            $sth = $this->db->prepare("select * from user_child where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    //Connect
    public function updateUserFacebook($uid, $fbId, $fbUser, $fbToken){
        try{
            $sth = $this->db->prepare("update users set fb_id=:fbId, fb_token=:fbToken, fb_user=:fbUser where uid =:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':fbId' => $fbId,
                ':fbToken' => $fbToken,
                ':fbUser' => $fbUser
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateUserTwitter($uid, $twitterId, $twitterUser, $twitterToken, $twitterImage){
        try{
            $sth = $this->db->prepare("update users set twitter_id=:twitterId, twitter_token=:twitterToken, twitter_user=:twitterUser, twitter_picture=:twitterImage where uid =:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':twitterId' => $twitterId,
                ':twitterToken' => $twitterToken,
                ':twitterUser' => $twitterUser,
                ':twitterImage' => $twitterImage
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateUserGoogle($uid, $googleId, $googleUser, $googleToken){
        try{
            $sth = $this->db->prepare("update users set google_id=:googleId, google_token=:googleToken, google_user=:googleUser where uid =:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':googleId' => $googleId,
                ':googleToken' => $googleToken,
                ':googleUser' => $googleUser
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateUserLinkedin($uid, $linkedinId, $linkedinToken, $linkedinUser){
        try{
            $sth = $this->db->prepare("update users set linkedin_id=:linkedinId, linkedin_token=:linkedinToken, linkedin_user=:linkedinUser where uid =:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':linkedinId' => $linkedinId,
                ':linkedinToken' => $linkedinToken,
                ':linkedinUser' => $linkedinUser
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    /*
     ------------------------------------
     * model: campaign
     * campaign, campaign page detail
     ------------------------------------
     */

    public function getCampaign(){
        try{
            $sth = $this->db->prepare("select * from campaign");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCampaignInProgress(){
        try{
            $sth = $this->db->prepare("select * from campaign where Datediff(CURDATE(), campaign_start_time) >= 0 and Datediff(CURDATE(), campaign_end_time) <= 0");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCampaignInProgressUser($uid){
        try{
            $sth = $this->db->prepare("select * from campaign c inner join user_campaign u on c.cid = u.cid where uid=:uid and buzzer_accept_status = 1 and Datediff(CURDATE(), campaign_start_time) >= 0 and Datediff(CURDATE(), campaign_end_time) <= 0");
            $sth->execute(array(
                ':uid' => $uid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCampaignInvited($uid){
        try{
            $sth = $this->db->prepare("select * from campaign c inner join user_campaign u on c.cid = u.cid where uid=:uid and buzzer_accept_status = 0 ");
            $sth->execute(array(
                ':uid' => $uid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCampaignById($cid){
        try{
            $sth = $this->db->prepare("select * from campaign where cid=:cid");
            $sth->execute(array(
                ':cid' => $cid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCampaignByIdAndSlug($cid, $slug){
        try{
            $sth = $this->db->prepare("select * from campaign where cid=:cid and campaign_url like :campaign_url");
            $sth->execute(array(
                ':cid' => $cid,
                ':campaign_url' => $slug
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addCampaign($uid, $title, $content, $url, $time, $start, $end){
        try{
            $sth = $this->db->prepare("insert into campaign(`uid`, `campaign_title`,`campaign_content`, `campaign_url`, `campaign_posted_time`, `campaign_start_time`, `campaign_end_time`)
            values( :uid, :campaign_title, :campaign_content, :campaign_url, :campaign_posted_time, :campaign_start_time, :campaign_end_time)");
            $sth->execute(array(
                ':uid' => $uid,
                ':campaign_title' => $title,
                ':campaign_content' => $content,
                ':campaign_url' => $url,
                ':campaign_posted_time' => $time,
                ':campaign_start_time' => $start,
                ':campaign_end_time' => $end
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateCampaign(){

    }

    public function deleteCampaign(){

    }

    /*
     ------------------------------------------
     * model: users
     * login,register,....
     -------------------------------------------
     */

    public function getUsersById($id){
        try{
            $sth = $this->db->prepare("select * from users where uid=:uid");
            $sth->execute(array(
                ':uid' => $id
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getUserByUsername($username){
        try{
            $sth = $this->db->prepare("select * from users where username like :username");
            $sth->execute(array(
                ':username' => $username
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getUserByEmail($email){
        try{
            $sth = $this->db->prepare("select * from users where email like :email");
            $sth->execute(array(
                ':email' => $email
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addUser($username, $password, $email, $pid, $sex, $birthday, $salary, $children){
        try{
            $sth = $this->db->prepare("insert into users(`username`, `password`, `email`, `pid`, `sex`, `birthday`, `salary`, `children`)
            values( :username, :password, :email, :pid, :sex, :birthday, :salary, :children)");
            $sth->execute(array(
                ':username' => $username,
                ':password' => $password,
                ':email' => $email,
                ':pid' => $pid,
                ':sex' => $sex,
                ':birthday' => $birthday,
                ':salary' => $salary,
                ':children' => $children
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateUsernamePassword($uid, $username, $password){
        try{
            $sth = $this->db->prepare("update users set username=:username, password=:password where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':username' => $username,
                ':password' => $password
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateUserPassword($uid, $password){
        try{
            $sth = $this->db->prepare("update users set password=:password where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':password' => $password
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateAccountInformation($uid, $first_name, $last_name, $gender, $about){
        try{
            $sth = $this->db->prepare("update users set first_name=:first_name, last_name=:last_name, sex=:sex, about=:about where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':first_name' => $first_name,
                ':last_name' => $last_name,
                ':sex' => $gender,
                ':about' => $about
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateAccountContact($uid, $email, $phone){
        try{
            $sth = $this->db->prepare("update users set email=:email, phone=:phone where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':email' => $email,
                ':phone' => $phone,
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateAccountAddress($uid, $company, $address_1, $address_2, $province){
        try{
            $sth = $this->db->prepare("update users set company=:company, address_1=:address_1, address_2=:address_2, pid=:pid where uid=:uid");
            $sth->execute(array(
                ':uid' => $uid,
                ':company' => $company,
                ':address_1' => $address_1,
                ':address_2' => $address_2,
                ':pid' => $province,
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getProvince(){
        try{
            $sth = $this->db->prepare("select * from province");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getSalary(){
        try{
            $sth = $this->db->prepare("select * from salary");
            $sth->execute();
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getUserByFacebookId($fb_id){
        try{
            $sth = $this->db->prepare("select * from users where fb_id like :fb_id");
            $sth->execute(array(
                ':fb_id' =>$fb_id
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addUserByFacebook($first_name, $last_name, $email, $sex, $birthday, $regiter_time, $fb_id, $fb_token){
        try{
            $sth = $this->db->prepare("insert into users(`first_name`, `last_name`, `email`, `sex`, `birthday`, `regiter_time`, `fb_id`, `fb_token`)
            values( :first_name, :last_name, :email, :sex, :birthday, :regiter_time, :fb_id, :fb_token)");
            $sth->execute(array(
                ':first_name' => $first_name,
                ':last_name' => $last_name,
                ':email' => $email,
                ':sex' => $sex,
                ':birthday' => $birthday,
                ':regiter_time' => $regiter_time,
                ':fb_id' => $fb_id,
                ':fb_token' => $fb_token
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }
    /*
     -----------------------------------
     * Comment
     -----------------------------------
     */
    public function getCommentByCid($cid){
        try{
            $sth = $this->db->prepare("select * from comment c inner join users u on c.uid = u.uid  where cid=:cid order by comment_time asc");
            $sth->execute(array(
                ':cid' => $cid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCommentParent($parent){
        try{
            $sth = $this->db->prepare("select * from comment c inner join users u on c.uid = u.uid where parent_cmid=:parent_cmid order by comment_time asc");
            $sth->execute(array(
                ':parent_cmid' => $parent
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addComment($cid, $parent_cmid, $uid, $comment_content, $comment_time, $comment_media, $comment_like){
        try{
            $sth = $this->db->prepare("insert into comment(`cid`, `parent_cmid`, `uid`, `comment_content`, `comment_time`, `comment_media`, `comment_like`)
            values( :cid, :parent_cmid, :uid, :comment_content, :comment_time, :comment_media, :comment_like)");
            $sth->execute(array(
                ':cid' => $cid,
                ':parent_cmid' => $parent_cmid,
                ':uid' => $uid,
                ':comment_content' => $comment_content,
                ':comment_time' => $comment_time,
                ':comment_media' => $comment_media,
                ':comment_like' => $comment_like
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateLike($id){
        try{
            $sth = $this->db->prepare("update comment set comment_like=comment_like+1 where cmid=:cmid");
            $sth->execute(array(
                ':cmid' => $id
            ));
            return true;
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function updateUnlike($id){
        try{
            $sth = $this->db->prepare("update comment set comment_like=comment_like-1 where cmid=:cmid");
            $sth->execute(array(
                ':cmid' => $id
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCommentLikeByCmidAndUid($cmid, $uid){
        try{
            $sth = $this->db->prepare("select * from comment_like where cmid=:cmid and uid=:uid");
            $sth->execute(array(
                ':cmid' => $cmid,
                ':uid' => $uid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function getCommentById($cmid){
        try{
            $sth = $this->db->prepare("select * from comment where cmid=:cmid");
            $sth->execute(array(
                ':cmid' => $cmid,
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function addCommentLike($cmid, $uid, $time){
        try{
            $sth = $this->db->prepare("insert into comment_like(`cmid`, `uid`, `time`)
            values( :cmid, :uid, :time)");
            $sth->execute(array(
                ':cmid' => $cmid,
                ':uid' => $uid,
                ':time' => $time
            ));
            return $this->db->lastInsertId();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    public function removeCommentLike($cmid, $uid){
        try{
            $sth = $this->db->prepare("delete from comment_like where cmid =:cmid and uid=:uid");
            $sth->execute(array(
                ':cmid' => $cmid,
                ':uid' => $uid,
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }

    /*
     -----------------------------------
     * Images
     -----------------------------------
     */
    public function getImagesByCid($cid){
        try{
            $sth = $this->db->prepare("select * from media where cid=:cid");
            $sth->execute(array(
                ':cid' => $cid
            ));
            return $sth->fetchAll();
        }
        catch (Exception $e){
            throw new Exception("Saving error! Try again!", 201);
        }
    }
}

?>